<?php


namespace App\Models;


use Illuminate\Support\Facades\DB;

class RollStat
{
    public $table = 'xpel_roll_stat';

    public function getRollStock(int $type)
    {
        if ($type === 1) {
            $data = DB::table('roll_restlen')
                ->leftJoin('roll_master', 'roll_master.roll_number', 'roll_restlen.roll_number')
                ->leftJoin('film_type_master', 'roll_master.film_type_id', 'film_type_master.id')
                ->where('roll_restlen.user_id', 1)
                ->where("restlen", '>', 0)
                ->whereNull('roll_restlen.deleted_at')
                ->select('roll_restlen.restlen', 'roll_master.length', 'film_type_master.product_type_id')
                ->get();
        } elseif ($type === 2) {
            $data = DB::table('roll_restlen')
                ->leftJoin('roll_master', 'roll_master.roll_number', 'roll_restlen.roll_number')
                ->leftJoin('film_type_master', 'roll_master.film_type_id', 'film_type_master.id')
                ->leftJoin('users', 'users.id', 'roll_restlen.user_id')
                ->where("restlen", '>', 0)
                ->where("users.user_type", 2)
                ->whereNull('roll_restlen.deleted_at')
                ->whereNull('users.deleted_at')
                ->where('users.status', 1)
                ->select('roll_restlen.restlen', 'roll_master.length', 'film_type_master.product_type_id')
                ->get();
        } elseif ($type === 3) {
            $data = DB::table('roll_restlen')
                ->leftJoin('roll_master', 'roll_master.roll_number', 'roll_restlen.roll_number')
                ->leftJoin('film_type_master', 'roll_master.film_type_id', 'film_type_master.id')
                ->leftJoin('users', 'users.id', 'roll_restlen.user_id')
                ->where("restlen", '>', 0)
                ->where("users.user_type", 3)
                ->whereNull('users.deleted_at')
                ->where('users.status', 1)
                ->whereNull('roll_restlen.deleted_at')
                ->select('roll_restlen.restlen', 'roll_master.length', 'film_type_master.product_type_id')
                ->get();
        }

        if (empty($data)) {
            return [
                'stock_1_total' => 0,
                'stock_2_total' => 0
            ];
        }

        $stock_1_total = 0;
        $stock_2_total = 0;
        foreach ($data as $value) {
            if ($value->length <= 0) {
                continue;
            }
            $len = round($value->restlen / $value->length, 2);
            if ($value->product_type_id == 1) {
                $stock_1_total += $len;
            }
            if ($value->product_type_id == 2) {
                $stock_2_total += $len;
            }
        }

        return [
            'stock_1_total' => round($stock_1_total, 2),
            'stock_2_total' => round($stock_2_total, 2),
        ];
    }

    /**
     * 获取移库数据
     *
     * @param string $start_date
     * @param string $end_date
     * @param int $type
     * @return array
     */
    public function getRollTransfer(string $start_date, string $end_date, int $type)
    {
        if ($type === 1) {
            $sql = 'SELECT sum(transfer_1) AS transfer_1_total, sum(transfer_2) AS transfer_2_total FROM xpel_roll_stat WHERE create_date>="%s"  AND create_date<="%s" AND user_id=1';
        } elseif ($type === 2) {
            $sql = 'SELECT sum(transfer_1) AS transfer_1_total, sum(transfer_2) AS transfer_2_total FROM xpel_roll_stat LEFT JOIN users ON users.id=xpel_roll_stat.user_id WHERE users.user_type=2 AND create_date>="%s" AND create_date<="%s"';
        } elseif ($type === 3) {
            $sql = 'SELECT sum(transfer_1) AS transfer_1_total, sum(transfer_2) AS transfer_2_total FROM xpel_roll_stat LEFT JOIN users ON users.id=xpel_roll_stat.user_id WHERE users.user_type=3 AND create_date>="%s" AND create_date<="%s"';
        }
        $sql = sprintf($sql, $start_date, $end_date);
        $result = DB::select($sql);

        return [
            'transfer_1_total' => !empty($result) && !empty($result[0]) && !empty($result[0]->transfer_1_total) ? $result[0]->transfer_1_total : 0,
            'transfer_2_total' => !empty($result) && !empty($result[0]) && !empty($result[0]->transfer_2_total) ? $result[0]->transfer_2_total : 0,
        ];
    }

    public function getRollReturn(string $start_date, string $end_date, int $type): array
    {
        if ($type === 1) {
            $sql = 'SELECT sum(return_1) AS return_1_total, sum(return_2) AS return_2_total FROM xpel_roll_stat WHERE create_date>="%s"  AND create_ate<="%s" AND user_id=1';
        } elseif ($type === 2) {
            $sql = 'SELECT sum(return_1) AS return_1_total, sum(return_2) AS return_2_total FROM xpel_roll_stat LEFT JOIN users ON users.id=xpel_roll_stat.user_id WHERE users.user_type=2 AND create_date>="%s" AND create_date<="%s"';
        } elseif ($type === 3) {
            $sql = 'SELECT sum(return_1) AS return_1_total, sum(return_2) AS return_2_total FROM xpel_roll_stat LEFT JOIN users ON users.id=xpel_roll_stat.user_id WHERE users.user_type=3 AND create_date>="%s" AND create_date<="%s"';
        }
        $sql = sprintf($sql, $start_date, $end_date);
        $result = DB::select($sql);

        return [
            'return_1_total' => !empty($result) && !empty($result[0]) && !empty($result[0]->return_1_total) ? $result[0]->return_1_total : 0,
            'return_2_total' => !empty($result) && !empty($result[0]) && !empty($result[0]->return_2_total) ? $result[0]->return_2_total : 0,
        ];
    }

    public function getRollSales(string $start_date, string $end_date, int $type): array
    {
        if ($type === 1) {
            $sql = 'SELECT sum(sale_1) AS sale_1_total, sum(sale_2) AS sale_2_total FROM xpel_roll_stat WHERE create_date>="%s"  AND create_date<="%s" AND user_id=1';
        } elseif ($type === 2) {
            $sql = 'SELECT sum(sale_1) AS sale_1_total, sum(sale_2) AS sale_2_total FROM xpel_roll_stat LEFT JOIN users ON users.id=xpel_roll_stat.user_id WHERE users.user_type=2 AND create_date>="%s" AND create_date<="%s"';
        } elseif ($type === 3) {
            $sql = 'SELECT sum(sale_1) AS sale_1_total, sum(sale_2) AS sale_2_total FROM xpel_roll_stat LEFT JOIN users ON users.id=xpel_roll_stat.user_id WHERE users.user_type=3 AND create_date>="%s" AND create_date<="%s"';
        }
        $sql = sprintf($sql, $start_date, $end_date);
        $result = DB::select($sql);

        return [
            'sale_1_total' => !empty($result) && !empty($result[0]) && !empty($result[0]->sale_1_total) ? $result[0]->sale_1_total : 0,
            'sale_2_total' => !empty($result) && !empty($result[0]) && !empty($result[0]->sale_2_total) ? $result[0]->sale_2_total : 0,
        ];
    }

    public function sales(int $type, int $user_type, string $start_date, string $end_date)
    {
        $select = $type == 1 ? 'SUM(sale_1) AS total' : 'SUM(sale_2) AS total';
        if ($user_type === 1) {
            $sql = 'SELECT %s, create_date AS date FROM xpel_roll_stat WHERE create_date>="%s"  AND create_date<="%s" AND user_id=1 GROUP BY create_date';
        } elseif ($user_type === 2) {
            $sql = 'SELECT %s, create_date AS date FROM xpel_roll_stat LEFT JOIN users ON users.id=xpel_roll_stat.user_id WHERE users.user_type=2 AND create_date>="%s" AND create_date<="%s" GROUP BY create_date';
        } elseif ($user_type === 3) {
            $sql = 'SELECT %s, create_date AS date FROM xpel_roll_stat LEFT JOIN users ON users.id=xpel_roll_stat.user_id WHERE users.user_type=3 AND create_date>="%s" AND create_date<="%s" GROUP BY create_date';
        }
        $sql = sprintf($sql, $select, $start_date, $end_date);
        $result = DB::select($sql);

        return $result ?? [];
    }

    public function transfer(int $type, int $user_type, string $start_date, string $end_date)
    {
        $select = $type == 1 ? 'SUM(transfer_1) AS total' : 'SUM(transfer_2) AS total';
        if ($user_type === 1) {
            $sql = 'SELECT %s, create_date AS date FROM xpel_roll_stat WHERE create_date>="%s"  AND create_date<="%s" AND user_id=1 GROUP BY create_date';
        } elseif ($user_type === 2) {
            $sql = 'SELECT %s, create_date AS date FROM xpel_roll_stat LEFT JOIN users ON users.id=xpel_roll_stat.user_id WHERE users.user_type=2 AND create_date>="%s" AND create_date<="%s" GROUP BY create_date';
        } elseif ($user_type === 3) {
            $sql = 'SELECT %s, create_date AS date FROM xpel_roll_stat LEFT JOIN users ON users.id=xpel_roll_stat.user_id WHERE users.user_type=3 AND create_date>="%s" AND create_date<="%s" GROUP BY create_date';
        }
        $sql = sprintf($sql, $select, $start_date, $end_date);
        $result = DB::select($sql);

        return $result ?? [];
    }

    public function return(int $type, int $user_type, string $start_date, string $end_date)
    {
        $select = $type == 1 ? 'SUM(return_1) AS total' : 'SUM(return_2) AS total';
        if ($user_type === 1) {
            $sql = 'SELECT %s, create_date AS date FROM xpel_roll_stat WHERE create_date>="%s"  AND create_date<="%s" AND user_id=1 GROUP BY create_date';
        } elseif ($user_type === 2) {
            $sql = 'SELECT %s, create_date AS date FROM xpel_roll_stat LEFT JOIN users ON users.id=xpel_roll_stat.user_id WHERE users.user_type=2 AND create_date>="%s" AND create_date<="%s" GROUP BY create_date';
        } elseif ($user_type === 3) {
            $sql = 'SELECT %s, create_date AS date FROM xpel_roll_stat LEFT JOIN users ON users.id=xpel_roll_stat.user_id WHERE users.user_type=3 AND create_date>="%s" AND create_date<="%s" GROUP BY create_date';
        }
        $sql = sprintf($sql, $select, $start_date, $end_date);
        $result = DB::select($sql);

        return $result ?? [];
    }
}